package Student;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;


public class Student_DAO implements IStuent_DAO{
	
	private String DRV = "oracle.jdbc.driver.OracleDriver";
	private String URL = "jdbc:oracle:thin:skyinline/123456@112.216.253.125:1521:xe"; 
	private String insertSQL = "insert into STUDENT values (STD_SEQ.nextval,?,?,?,?,?,?,?,?)"; 
	private String selectSQL = "select * from STUDENT order by STD_NO desc";
	private String updateSQL = "update STUDENT set USER_PW=?, STD_NAME=?, STD_TEL=?, STD_ADDR=?, DEPT_NO=?, PROF_NO=?, SUB_NO=?, TOT_POINT=?, sysdate where STD_NO=? ";
	private String deleteSQL = "delete STUDENT where STD_NO=?";
	
	
	private Connection openDatabase(){
		try{Class.forName(DRV);
		return DriverManager.getConnection(URL);
		}catch(Exception ex){
			ex.printStackTrace();
		}
		return null;
	}
	
	private void closeDatabase
	(Connection c, PreparedStatement p, ResultSet r)
	{if(r != null)
		try{r.close(); r = null;}
	catch(Exception e){}
	if(p != null)
		try{p.close(); p = null;}
	catch(Exception e){}
	if(c != null)
		try{c.close(); c = null;}
		catch(Exception e){}
	}
	

	@Override
	public boolean insertStudent(StudentVO s) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		boolean result = false;
		
		try{conn = this.openDatabase();
		pstmt = conn.prepareStatement(insertSQL);
		pstmt.setString(1, s.getSTD_NO());
		pstmt.setString(2, s.getUSER_PW());
		pstmt.setString(3, s.getSTD_NAME());
		pstmt.setString(4, s.getSTD_TEL());
		pstmt.setString(5, s.getSTD_ADDR());
		pstmt.setString(6, s.getDEPT_NO());
		pstmt.setString(7, s.getPROF_NO());
		pstmt.setString(8, s.getSUB_NO());
		pstmt.setString(9, s.getTOT_POINT());
		pstmt.execute();
		result = true;
		}catch(Exception ex){
			ex.printStackTrace();
		}finally{
			closeDatabase(conn,pstmt,null);
		}
		
		return result;
	}
	
	@Override
	public List<StudentVO> selectStudent(){
	Connection conn = null;
	PreparedStatement pstmt = null;
	ResultSet rs = null;
	List<StudentVO> result = new ArrayList<>();
	
	try{conn = openDatabase();
	pstmt = conn.prepareStatement(selectSQL);
	rs = pstmt.executeQuery();
	while(rs.next()){
		StudentVO s = new StudentVO();
		s.setSTD_NO(rs.getString("STD_NO"));
		s.setUSER_PW(rs.getString("USER_PW"));
		s.setSTD_NAME(rs.getString("STD_NAME"));
		s.setSTD_TEL(rs.getString("STD_TEL"));
		s.setSTD_ADDR(rs.getString("STD_ADDR"));
		s.setDEPT_NO(rs.getString("DEPT_NO"));
		s.setPROF_NO(rs.getString("PROF_NO"));
		s.setSUB_NO(rs.getString("SUB_NO"));
		s.setTOT_POINT(rs.getString("TOT_POINT"));
		
		result.add(s);
	}
	}catch(Exception ex){
		ex.printStackTrace();
	}finally{
		closeDatabase(conn, pstmt, rs);
	}	
	
		return result;
	}
	
	@Override
	public boolean updateStudent(StudentVO s) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		boolean result = false;
		
		
		
		try{conn = openDatabase();
		pstmt = conn.prepareStatement(updateSQL);
				
		pstmt.setString(1, s.getUSER_PW() ); //비밀번호
		pstmt.setString(2, s.getSTD_NAME() ); // 이름
		pstmt.setString(3, s.getSTD_TEL() ); // 전화번호
		pstmt.setString(4, s.getSTD_ADDR() ); // 주소
		pstmt.setString(5, s.getDEPT_NO());	// 학과
		pstmt.setString(6, s.getPROF_NO() ); // 교수
		pstmt.setString(7, s.getSUB_NO() );	//과목정보
		pstmt.setString(8, s.getSTD_NO() );	//학번
		pstmt.setString(9, s.getTOT_POINT() );	//총학점
		
					
		pstmt.executeUpdate();
		result = true;
						
		}catch(Exception ex){
			ex.printStackTrace();
		}finally{
			closeDatabase(conn, pstmt, null);
			
		}
		
		
		return result;
		
	}	
	@Override
	public boolean deleteStudent(StudentVO s) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		boolean result = false;
		try{conn = openDatabase();
		pstmt = conn.prepareStatement(deleteSQL);
		pstmt.setString(1, s.getSTD_NO());
		pstmt.executeUpdate();
		
		result = true;
	
		}catch(Exception ex){
			ex.printStackTrace();
		}finally{
			closeDatabase(conn, pstmt, null);}
		return result;
	}}
	